package com.unlcn.ils.wms.backend.service.inventorymanager.impl;

import cn.huiyunche.commons.domain.PageVo;
import cn.huiyunche.commons.domain.ResultDTOWithPagination;
import cn.huiyunche.commons.exception.BusinessException;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Maps;
import com.unlcn.ils.wms.backend.enums.*;
import com.unlcn.ils.wms.backend.service.inventorymanager.WmsInventoryVerificationService;
import com.unlcn.ils.wms.backend.util.BrowerEncodeingUtils;
import com.unlcn.ils.wms.backend.util.DateUtils;
import com.unlcn.ils.wms.base.businessDTO.outbound.WmsOutboundTaskDTOForQuery;
import com.unlcn.ils.wms.base.dto.WmsInventoryLocationManageDTO;
import com.unlcn.ils.wms.base.dto.WmsInventoryManagerParamDTO;
import com.unlcn.ils.wms.base.dto.WmsInventoryManagerResultDTO;
import com.unlcn.ils.wms.base.mapper.additional.WmsInventoryExtMapper;
import com.unlcn.ils.wms.base.mapper.additional.WmsInventoryLocationExtMapper;
import com.unlcn.ils.wms.base.mapper.additional.wmsOutbound.WmsOutboundTaskExtMapper;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * 2018-3-28 新需求:WMS 的入库记录/出库记录/库存记录查询(可能含所有仓库数据)
 * </p>
 */
@Service
public class WmsInventoryVerificationServiceImpl implements WmsInventoryVerificationService {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private WmsInventoryLocationExtMapper wmsInventoryLocationExtMapper;

    @Autowired
    private WmsOutboundTaskExtMapper wmsOutboundTaskExtMapper;

    @Autowired
    private WmsInventoryExtMapper wmsInventoryExtMapper;

    /**
     * 入库记录管理界面列表
     *
     * @param dto 参数
     * @return 返回值
     */
    @Override
    public ResultDTOWithPagination<List<Map<String, Object>>> getInboundManageRecordsByCustomer(WmsInventoryManagerParamDTO dto) throws Exception {
        if (logger.isInfoEnabled()) {
            logger.info("WmsInventoryVerificationServiceImpl.getInboundManageRecordsByCustomer params:", JSONObject.toJSONString(dto));
        }
        if (dto == null)
            throw new BusinessException("传入参数不能为空!");
        if (StringUtils.isBlank(dto.getCustomerCode()))
            throw new BusinessException("传入的客户Code不能为空!");
        if (StringUtils.isBlank(dto.getUserId()))
            throw new BusinessException("请登录后进行该操作!");
        //
        HashMap<String, Object> params = Maps.newHashMap();
        params.put("start", dto.getStartIndex());
        params.put("end", dto.getPageSize());
        getInboundParams(dto, params);
        //去除sql严格校验
        wmsInventoryLocationExtMapper.updateSQLMode();
        List<Map<String, Object>> data = wmsInventoryLocationExtMapper.selectInboundRecordsForManageByParam(params);
        Long count = wmsInventoryLocationExtMapper.countInboundRecordsForManageByParam(params);
        ResultDTOWithPagination<List<Map<String, Object>>> result = new ResultDTOWithPagination<>();
        PageVo pageVo = new PageVo();
        pageVo.setOrder(dto.getOrder());
        pageVo.setPageSize(dto.getPageSize());
        pageVo.setPageNo(dto.getPageNo());
        pageVo.setTotalRecord(Math.toIntExact(count));
        result.setData(data);
        result.setPageVo(pageVo);
        return result;

    }


    /**
     * 入库记录导出
     *
     * @param dto      参数封装
     * @param request  请求体
     * @param response 相应体
     */
    @Override
    public void getInboundManageRecordsByCustomerAndExport(WmsInventoryManagerParamDTO dto,
                                                           HttpServletRequest request,
                                                           HttpServletResponse response) throws Exception {
        if (logger.isInfoEnabled()) {
            logger.info("WmsInventoryVerificationServiceImpl.getInboundManageRecordsByCustomer params:", JSONObject.toJSONString(dto));
        }
        if (dto == null)
            throw new BusinessException("传入参数不能为空!");
        if (StringUtils.isBlank(dto.getCustomerCode()))
            throw new BusinessException("传入的客户CODE不能为空!");
        if (StringUtils.isBlank(dto.getUserId()))
            throw new BusinessException("请登录后进行该操作!");
        HashMap<String, Object> params = Maps.newHashMap();
        getInboundParams(dto, params);
        //去除sql严格校验
        wmsInventoryLocationExtMapper.updateSQLMode();
        List<Map<String, Object>> data = wmsInventoryLocationExtMapper.selectInboundRecordsForManageByParam(params);
        if (CollectionUtils.isEmpty(data)) {
            throw new BusinessException("未查询到对应条件的数据");
        }
        HSSFWorkbook workbook = null;
        if (WmsCustomerWarehouseCodeEnum.JL_CQ.getCustomerCode().equals(dto.getCustomerCode())) {
            //1.导出excel头
            String[] hearders = new String[]{
                    "车架号(VIN码)", "车型", "收货完成时间", "入库确认时间", "收货操作人", "入库确认人", "仓库CODE", "位置"
            };
            //设置值
            workbook = updateInboundExportToExcelForCQ("入库记录数据导出", hearders, data, "yyyy-MM-dd HH:mm:ss");

        } else {
            //1.导出excel头
            String[] headers = new String[]{
                    "车架号(VIN码)", "车型", "物料代码", "物料名称", "颜色代码", "颜色",
                    "收货完成时间", "入库确认时间", "收货操作人", "入库确认人", "仓库CODE", "位置"
            };
            //设置值
            workbook = updateInboundExportToExcelForJM("入库记录数据导出", headers, data, "yyyy-MM-dd HH:mm:ss");
        }
        if (workbook != null) {
            //输出流--提供下载
            updateSetOutStream(request, response, workbook);
        }
    }


    /**
     * 出库记录管理界面列表
     *
     * @param dto 参数
     * @return 返回值
     */
    @Override
    public ResultDTOWithPagination<List<WmsOutboundTaskDTOForQuery>> getOutboundManageRecordsByCustomer(WmsInventoryManagerParamDTO dto) throws Exception {
        if (logger.isInfoEnabled()) {
            logger.info("WmsInventoryVerificationServiceImpl.getInboundManageRecordsByCustomer params:", JSONObject.toJSONString(dto));
        }
        if (dto == null)
            throw new BusinessException("传入参数不能为空!");
        if (StringUtils.isBlank(dto.getCustomerCode()))
            throw new BusinessException("传入的客户Code不能为空!");
        if (StringUtils.isBlank(dto.getUserId()))
            throw new BusinessException("请登录后进行该操作!");
        //
        HashMap<String, Object> params = Maps.newHashMap();
        params.put("start", dto.getStartIndex());
        params.put("end", dto.getPageSize());
        getOutboundParams(dto, params);
        wmsOutboundTaskExtMapper.updateSQLmode();
        //List<WmsOutboundTaskDTOForQuery> list = wmsOutboundTaskExtMapper.listOutboundRecordForManageByParam(params);
        List<WmsOutboundTaskDTOForQuery> list = wmsOutboundTaskExtMapper.listOutboundRecordForManageByParamWithNotice(params);
        //Long count = wmsOutboundTaskExtMapper.countOutboundRecordForManageByParam(params);
        Long count = wmsOutboundTaskExtMapper.countOutboundRecordForManageByParamWithNotice(params);
        ResultDTOWithPagination<List<WmsOutboundTaskDTOForQuery>> result = new ResultDTOWithPagination<>();
        PageVo pageVo = new PageVo();
        pageVo.setTotalRecord(Math.toIntExact(count));
        pageVo.setOrder(dto.getOrder());
        pageVo.setPageNo(dto.getPageNo());
        pageVo.setPageSize(dto.getPageSize());
        result.setPageVo(pageVo);
        result.setData(list);
        return result;
    }


    /**
     * 出库记录导出
     *
     * @param dto      参数封装
     * @param request  请求体
     * @param response 相应体
     */
    @Override
    public void getOutboundManageRecordsByCustomerAndExport(WmsInventoryManagerParamDTO dto,
                                                            HttpServletRequest request,
                                                            HttpServletResponse response) throws Exception {
        if (logger.isInfoEnabled()) {
            logger.info("WmsInventoryVerificationServiceImpl.getOutboundManageRecordsByCustomerAndExport params:", JSONObject.toJSONString(dto));
        }
        if (dto == null)
            throw new BusinessException("传入参数不能为空!");
        if (StringUtils.isBlank(dto.getCustomerCode()))
            throw new BusinessException("传入的客户Code不能为空!");
        if (StringUtils.isBlank(dto.getUserId()))
            throw new BusinessException("请登录后进行该操作!");
        //
        HashMap<String, Object> params = Maps.newHashMap();
        getOutboundParams(dto, params);
        wmsOutboundTaskExtMapper.updateSQLmode();
        //List<WmsOutboundTaskDTOForQuery> data = wmsOutboundTaskExtMapper.listOutboundRecordForManageByParam(params);
        List<WmsOutboundTaskDTOForQuery> data = wmsOutboundTaskExtMapper.listOutboundRecordForManageByParamWithNotice(params);
        if (CollectionUtils.isEmpty(data)) {
            throw new BusinessException("未查询到对应条件的数据");
        }
        HSSFWorkbook workbook = null;
        if (WmsCustomerWarehouseCodeEnum.JL_CQ.getCustomerCode().equals(dto.getCustomerCode())) {
            //1.导出excel头
            String[] hearders = new String[]{
                    "车架号(VIN码)", "车型", "货主", "出库确认时间", "出库确认人", "仓库CODE"
            };
            //设置值
            workbook = updateOutboundExportToExcelForCQ("出库记录数据导出", hearders, data, "yyyy-MM-dd HH:mm:ss");
        } else {
            //1.导出excel头
            String[] headers = new String[]{
                    "车架号(VIN码)", "车型", "物料代码", "物料名称", "颜色代码", "颜色",
                    "货主", "出库确认时间", "出库确认人", "仓库CODE"
            };
            //设置值
            workbook = updateOutboundExportToExcelForJM("出库记录数据导出", headers, data, "yyyy-MM-dd HH:mm:ss");
        }
        if (workbook != null) {
            //输出流--提供下载
            updateSetOutStream(request, response, workbook);
        }
    }

    /**
     * @param dto 参数封装
     * @return 返回值
     */
    @Override
    public ResultDTOWithPagination<List<WmsInventoryManagerResultDTO>> getInventoryManageRecordsByCustomer(WmsInventoryManagerParamDTO dto) throws Exception {
        if (logger.isInfoEnabled()) {
            logger.info("WmsInventoryVerificationServiceImpl.getInventoryManageRecordsByCustomer params:", JSONObject.toJSONString(dto));
        }
        if (dto == null)
            throw new BusinessException("传入参数不能为空!");
        if (StringUtils.isBlank(dto.getCustomerCode()))
            throw new BusinessException("传入的客户Code不能为空!");
        if (StringUtils.isBlank(dto.getUserId()))
            throw new BusinessException("请登录后进行该操作!");
        //
        HashMap<String, Object> params = Maps.newHashMap();
        params.put("start", dto.getStartIndex());
        params.put("end", dto.getPageSize());
        getInventoryParams(dto, params);
        //List<WmsInventoryManagerResultDTO> data = wmsInventoryExtMapper.selectInventoryRecordForManageByParam(params);
        List<WmsInventoryManagerResultDTO> data = wmsInventoryExtMapper.selectInventoryRecordForManageByParamWithNotice(params);
        //设置库龄
        updateSetWarehouseAge(data);
        //Long count = wmsInventoryExtMapper.countInventoryRecordForManageByParam(params);
        Long count = wmsInventoryExtMapper.countInventoryRecordForManageByParamWithNotice(params);
        ResultDTOWithPagination<List<WmsInventoryManagerResultDTO>> result = new ResultDTOWithPagination<>();
        PageVo pageVo = new PageVo();
        pageVo.setPageNo(dto.getPageNo());
        pageVo.setPageSize(dto.getPageSize());
        pageVo.setTotalRecord(Math.toIntExact(count));
        pageVo.setOrder(dto.getOrder());
        result.setData(data);
        result.setPageVo(pageVo);
        return result;
    }

    /**
     * 库存记录导出
     *
     * @param dto      参数封装
     * @param request  请求体
     * @param response 相应体
     */
    @Override
    public void getInventoryManageRecordsByCustomerAndExport(WmsInventoryManagerParamDTO dto,
                                                             HttpServletRequest request,
                                                             HttpServletResponse response) throws Exception {
        if (logger.isInfoEnabled()) {
            logger.info("WmsInventoryVerificationServiceImpl.getInventoryManageRecordsByCustomerAndExport params:", JSONObject.toJSONString(dto));
        }
        if (dto == null)
            throw new BusinessException("传入参数不能为空!");
        if (StringUtils.isBlank(dto.getCustomerCode()))
            throw new BusinessException("传入的客户Code不能为空!");
        if (StringUtils.isBlank(dto.getUserId()))
            throw new BusinessException("请登录后进行该操作!");
        //
        HashMap<String, Object> params = Maps.newHashMap();
        getInventoryParams(dto, params);
        //List<WmsInventoryManagerResultDTO> data = wmsInventoryExtMapper.selectInventoryRecordForManageByParam(params);
        List<WmsInventoryManagerResultDTO> data = wmsInventoryExtMapper.selectInventoryRecordForManageByParamWithNotice(params);
        if (CollectionUtils.isEmpty(data)) {
            throw new BusinessException("未查询到对应条件的数据");
        }
        //设置库龄
        updateSetWarehouseAge(data);
        HSSFWorkbook workbook = null;
        if (WmsCustomerWarehouseCodeEnum.JL_CQ.getCustomerCode().equals(dto.getCustomerCode())) {
            //1.导出excel头
            String[] headers = new String[]{
                    "车架号(VIN码)", "车型", "货主", "入库时间", "在库库龄", "库区", "库位", "仓库CODE"
            };
            //设置值
            workbook = updateInventoryExportToExcelForCQ("库存记录数据导出", headers, data, "yyyy-MM-dd HH:mm:ss");
        } else {
            //1.导出excel头
            String[] headers = new String[]{
                    "车架号(VIN码)", "车型", "物料代码", "物料名称", "颜色代码", "颜色",
                    "货主", "入库时间", "在库库龄", "库区", "库位", "仓库CODE"
            };
            //设置值
            workbook = updateInventoryExportToExcelForJM("库存记录数据导出", headers, data, "yyyy-MM-dd HH:mm:ss");
        }
        if (workbook != null) {
            //输出流--提供下载
            updateSetOutStream(request, response, workbook);
        }
    }

    private HSSFWorkbook updateInventoryExportToExcelForJM(String title,
                                                           String[] headers,
                                                           List<WmsInventoryManagerResultDTO> data,
                                                           String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCellStyle style2 = getHssfCellStyle(workbook, style);
        //// 声明一个画图的顶级管理器
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //// 定义注释的大小和位置,详见文档
        //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        //        0, 0, 0, (short) 4, 2, (short) 6, 5));
        //// 设置注释内容
        //comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        //// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(style);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator<WmsInventoryManagerResultDTO> iterator = data.iterator();
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);//创建行
            WmsInventoryManagerResultDTO next = iterator.next();
            //对应头  设置cell的值
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                WmsInventoryLocationManageDTO invlocDTO = next.getWmsInventoryLocationDTOList().get(0);
                switch (i) {
                    //"车架号(VIN码)", "车型", "物料代码", "物料名称", "颜色代码", "颜色",
                    //"货主", "入库时间", "在库库龄",  "库区", "库位", "仓库CODE"
                    case 0: {
                        //"车架号(VIN码)"
                        cell.setCellValue(updateSetCellValue(invlocDTO.getInvlocVin()));
                        break;
                    }
                    case 1: {
                        // "车型"
                        cell.setCellValue(updateSetCellValue(next.getInvVehicleSpecName()));
                        break;
                    }
                    case 2: {
                        //"物料代码"
                        cell.setCellValue(updateSetCellValue(next.getMaterialCode()));
                        break;
                    }
                    case 3: {
                        //"物料名称"
                        cell.setCellValue(updateSetCellValue(next.getMaterialName()));
                        break;
                    }
                    case 4: {
                        //"颜色代码"
                        cell.setCellValue(updateSetCellValue(next.getColourCode()));
                        break;
                    }
                    case 5: {
                        //"颜色"
                        cell.setCellValue(updateSetCellValue(next.getColourName()));
                        break;
                    }
                    case 6: {
                        //"货主"
                        cell.setCellValue(updateSetCellValue(invlocDTO.getInvlocCustomerName()));
                        break;
                    }
                    case 7: {
                        //"入库时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getGmtCreate())));
                        break;
                    }
                    case 8: {
                        //"在库库龄"
                        cell.setCellValue(updateSetCellValue(next.getWarehouseAge()));
                        break;
                    }
                    case 9: {
                        //"库区"
                        cell.setCellValue(updateSetCellValue(invlocDTO.getInvlocZoneCode()));
                        break;
                    }
                    case 10: {
                        //"库位"
                        cell.setCellValue(updateSetCellValue(invlocDTO.getInvlocLocCode()));
                        break;
                    }
                    case 11: {
                        //"库区"
                        cell.setCellValue(updateSetCellValue(next.getInvWhCode()));
                        break;
                    }
                    default:
                        break;
                }
            }
        }
        return workbook;

    }


    /**
     * @param title   标题
     * @param headers 列名
     * @param data    数据
     * @param pattern 日期格式
     * @return 返回值
     */
    private HSSFWorkbook updateInventoryExportToExcelForCQ(String title,
                                                           String[] headers,
                                                           List<WmsInventoryManagerResultDTO> data,
                                                           String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCellStyle style2 = getHssfCellStyle(workbook, style);
        //// 声明一个画图的顶级管理器
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //// 定义注释的大小和位置,详见文档
        //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        //        0, 0, 0, (short) 4, 2, (short) 6, 5));
        //// 设置注释内容
        //comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        //// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(style);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator<WmsInventoryManagerResultDTO> iterator = data.iterator();
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);//创建行
            WmsInventoryManagerResultDTO next = iterator.next();
            //对应头  设置cell的值
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                WmsInventoryLocationManageDTO invlocDto = next.getWmsInventoryLocationDTOList().get(0);
                switch (i) {
                    //"车架号(VIN码)", "车型", "货主", "入库时间", "在库库龄", "库区", "库位", "仓库CODE"
                    case 0: {
                        //"车架号(VIN码)"
                        cell.setCellValue(updateSetCellValue(invlocDto.getInvlocVin()));
                        break;
                    }
                    case 1: {
                        // "车型"
                        cell.setCellValue(updateSetCellValue(next.getInvVehicleSpecName()));
                        break;
                    }
                    case 2: {
                        //"货主"
                        cell.setCellValue(updateSetCellValue(invlocDto.getInvlocCustomerName()));
                        break;
                    }
                    case 3: {
                        //"入库时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getGmtCreate())));
                        break;
                    }
                    case 4: {
                        //"在库库龄"
                        cell.setCellValue(updateSetCellValue(next.getWarehouseAge()));
                        break;
                    }
                    case 5: {
                        //"库区"
                        cell.setCellValue(updateSetCellValue(invlocDto.getInvlocZoneCode()));
                        break;
                    }
                    case 6: {
                        //"库位"
                        cell.setCellValue(updateSetCellValue(invlocDto.getInvlocLocCode()));
                        break;
                    }
                    case 7: {
                        //"仓库CODE"
                        cell.setCellValue(updateSetCellValue(next.getInvWhCode()));
                        break;
                    }
                    default:
                        break;
                }
            }
        }
        return workbook;

    }

    private void getCustomerWarehouseCodeSQLSplit(HashMap<String, Object> params, List<WmsCustomerWarehouseCodeEnum> enums) {
        if (CollectionUtils.isNotEmpty(enums)) {
            StringBuilder sb = new StringBuilder();
            sb.append("(");
            enums.forEach(code -> {
                sb.append("t.od_wh_code ='").append(code.getWarehouseCode()).append("' or ");
            });
            sb.append(")");
            int lastIndexOf = StringUtils.lastIndexOf(sb, "or )");
            String sql_split = sb.substring(0, lastIndexOf);
            params.put("customerCode", sql_split + " )");
        }
    }

    /**
     * @param title             sheet标题
     * @param headers           表头
     * @param wmsInboundRecords 入库记录
     * @param pattern           @Title exportExcel
     */
    private HSSFWorkbook updateInboundExportToExcelForCQ(String title, String[] headers,
                                                         List<Map<String, Object>> wmsInboundRecords, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCellStyle style2 = getHssfCellStyle(workbook, style);
        //// 声明一个画图的顶级管理器
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //// 定义注释的大小和位置,详见文档
        //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        //        0, 0, 0, (short) 4, 2, (short) 6, 5));
        //// 设置注释内容
        //comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        //// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(style);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator<Map<String, Object>> iterator = wmsInboundRecords.iterator();
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);//创建行
            Map<String, Object> next = iterator.next();
            //对应头  设置cell的值
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                switch (i) {
                    case 0: {
                        //"车架号(VIN码)"
                        cell.setCellValue(updateSetCellValue(next.get("odd_vin")));
                        break;
                    }
                    case 1: {
                        // "车型"
                        cell.setCellValue(updateSetCellValue(next.get("odd_vehicle_spec_name")));
                        break;
                    }
                    case 2: {
                        //"收货完成时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate((Date) next.get("od_consignee_date"))));
                        break;
                    }
                    case 3: {
                        //"入库确认时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate((Date) next.get("rkDate"))));
                        break;
                    }
                    case 4: {
                        //"收货操作人"
                        cell.setCellValue(updateSetCellValue(next.get("od_consignee_name")));
                        break;
                    }
                    case 5: {
                        //"入库确认人"
                        cell.setCellValue(updateSetCellValue(next.get("inboundConfirmUser")));
                        break;
                    }
                    case 6: {
                        //"仓库code"
                        cell.setCellValue(updateSetCellValue(next.get("odd_wh_code")));
                        break;
                    }
                    case 7: {
                        //"位置"
                        cell.setCellValue(updateSetCellValue(next.get("od_wh_name"))
                                + updateSetCellValue(next.get("odd_wh_zone_name"))
                                + updateSetCellValue(next.get("odd_wh_loc_code")));
                        break;
                    }
                    default:
                        break;
                }
            }
        }
        return workbook;
    }


    /**
     * @param title             sheet标题
     * @param headers           表头
     * @param wmsInboundRecords 入库记录
     * @param pattern           @Title exportExcel
     */
    private HSSFWorkbook updateInboundExportToExcelForJM(String title, String[] headers,
                                                         List<Map<String, Object>> wmsInboundRecords, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCellStyle style2 = getHssfCellStyle(workbook, style);
        //// 声明一个画图的顶级管理器
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //// 定义注释的大小和位置,详见文档
        //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        //        0, 0, 0, (short) 4, 2, (short) 6, 5));
        //// 设置注释内容
        //comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        //// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(style);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator<Map<String, Object>> iterator = wmsInboundRecords.iterator();
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);//创建行
            Map<String, Object> next = iterator.next();
            //对应头  设置cell的值
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                switch (i) {
                    case 0: {
                        //"车架号(VIN码)"
                        cell.setCellValue(updateSetCellValue(next.get("odd_vin")));
                        break;
                    }
                    case 1: {
                        // "车型"
                        cell.setCellValue(updateSetCellValue(next.get("odd_vehicle_spec_name")));
                        break;
                    }
                    case 2: {
                        //"物料代码"
                        cell.setCellValue(updateSetCellValue(next.get("odd_material_code")));
                        break;
                    }
                    case 3: {
                        //"物料名称"
                        cell.setCellValue(updateSetCellValue(next.get("odd_material_name")));
                        break;
                    }
                    case 4: {
                        //"颜色代码"
                        cell.setCellValue(updateSetCellValue(next.get("odd_car_colour_code")));
                        break;
                    }
                    case 5: {
                        //"颜色"
                        cell.setCellValue(updateSetCellValue(next.get("odd_car_colour")));
                        break;
                    }
                    case 6: {
                        //"收货完成时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate((Date) next.get("od_consignee_date"))));
                        break;
                    }
                    case 7: {
                        //"入库确认时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate((Date) next.get("rkDate"))));
                        break;
                    }
                    case 8: {
                        //"收货操作人"
                        cell.setCellValue(updateSetCellValue(next.get("od_consignee_name")));
                        break;
                    }
                    case 9: {
                        //"入库确认人"
                        cell.setCellValue(updateSetCellValue(next.get("inboundConfirmUser")));
                        break;
                    }
                    case 10: {
                        //"仓库code"
                        cell.setCellValue(updateSetCellValue(next.get("odd_wh_code")));
                        break;
                    }
                    case 11: {
                        //"位置"
                        cell.setCellValue(updateSetCellValue(next.get("od_wh_name"))
                                + updateSetCellValue(next.get("odd_wh_zone_name"))
                                + updateSetCellValue(next.get("odd_wh_loc_code")));
                        break;
                    }
                    default:
                        break;
                }
            }
        }
        return workbook;
    }


    /**
     * @param title   标题
     * @param headers 列名
     * @param data    数据
     * @param pattern 日期格式
     * @return 返回值
     */
    private HSSFWorkbook updateOutboundExportToExcelForCQ(String title,
                                                          String[] headers,
                                                          List<WmsOutboundTaskDTOForQuery> data,
                                                          String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCellStyle style2 = getHssfCellStyle(workbook, style);
        //// 声明一个画图的顶级管理器
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //// 定义注释的大小和位置,详见文档
        //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        //        0, 0, 0, (short) 4, 2, (short) 6, 5));
        //// 设置注释内容
        //comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        //// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(style);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator<WmsOutboundTaskDTOForQuery> iterator = data.iterator();
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);//创建行
            WmsOutboundTaskDTOForQuery next = iterator.next();
            //对应头  设置cell的值
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                switch (i) {
                    //"车架号(VIN码)", "车型", "货主", "出库确认时间", "出库确认人", "仓库CODE"
                    case 0: {
                        //"车架号(VIN码)"
                        cell.setCellValue(updateSetCellValue(next.getOtVin()));
                        break;
                    }
                    case 1: {
                        // "车型"
                        cell.setCellValue(updateSetCellValue(next.getOtVehicleSpecName()));
                        break;
                    }
                    case 2: {
                        //"货主"
                        cell.setCellValue(updateSetCellValue(next.getOtCustomerName()));
                        break;
                    }
                    case 3: {
                        //"出库确认时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getOtBlConfirmTime())));
                        break;
                    }
                    case 4: {
                        //"出库确认人"
                        cell.setCellValue(updateSetCellValue(next.getModifyUserName()));
                        break;
                    }
                    case 5: {
                        //"仓库CODE"
                        cell.setCellValue(updateSetCellValue(next.getOtWhCode()));
                        break;
                    }
                    default:
                        break;
                }
            }
        }
        return workbook;
    }


    /**
     * @param title   标题
     * @param headers 列名
     * @param data    数据
     * @param pattern 日期格式
     * @return 返回值
     */
    private HSSFWorkbook updateOutboundExportToExcelForJM(String title,
                                                          String[] headers,
                                                          List<WmsOutboundTaskDTOForQuery> data,
                                                          String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCellStyle style2 = getHssfCellStyle(workbook, style);
        //// 声明一个画图的顶级管理器
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //// 定义注释的大小和位置,详见文档
        //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        //        0, 0, 0, (short) 4, 2, (short) 6, 5));
        //// 设置注释内容
        //comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        //// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(style);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator<WmsOutboundTaskDTOForQuery> iterator = data.iterator();
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);//创建行
            WmsOutboundTaskDTOForQuery next = iterator.next();
            //对应头  设置cell的值
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                switch (i) {
                    //"车架号(VIN码)", "车型", "物料代码", "物料名称", "颜色代码", "颜色",
                    //"货主", "出库确认时间", "出库确认人", "仓库CODE"
                    case 0: {
                        //"车架号(VIN码)"
                        cell.setCellValue(updateSetCellValue(next.getOtVin()));
                        break;
                    }
                    case 1: {
                        // "车型"
                        cell.setCellValue(updateSetCellValue(next.getOtVehicleSpecName()));
                        break;
                    }
                    case 2: {
                        //"物料代码"
                        cell.setCellValue(updateSetCellValue(next.getMaterialCode()));
                        break;
                    }
                    case 3: {
                        //"物料名称"
                        cell.setCellValue(updateSetCellValue(next.getConfiguration()));
                        break;
                    }
                    case 4: {
                        //"颜色代码"
                        cell.setCellValue(updateSetCellValue(next.getColourCode()));
                        break;
                    }
                    case 5: {
                        //"颜色"
                        cell.setCellValue(updateSetCellValue(next.getColourName()));
                        break;
                    }
                    case 6: {
                        //"货主"
                        cell.setCellValue(updateSetCellValue(next.getOtCustomerName()));
                        break;
                    }
                    case 7: {
                        //"出库确认时间"
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getOtBlConfirmTime())));
                        break;
                    }
                    case 8: {
                        //"出库确认人"
                        cell.setCellValue(updateSetCellValue(next.getModifyUserName()));
                        break;
                    }
                    case 9: {
                        //"仓库CODE"
                        cell.setCellValue(updateSetCellValue(next.getOtWhCode()));
                        break;
                    }
                    default:
                        break;
                }
            }
        }
        return workbook;
    }


    /**
     * 设置样式
     *
     * @param workbook 工作簿
     * @param style    样式
     * @return 返回值
     */
    private HSSFCellStyle getHssfCellStyle(HSSFWorkbook workbook, HSSFCellStyle style) {
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.WHITE.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        return style2;
    }


    /**
     * 判断字符串设置值--非空过滤
     *
     * @param object 传入参数
     * @return 返回值
     */
    private String updateSetCellValue(Object object) {
        if (!Objects.equals(object, null)) {
            String value = object.toString();
            if (StringUtils.isNotBlank(value)) {
                return value;
            }
        }
        return "";
    }

    /**
     * 时间格式化--非空过滤
     *
     * @param date 传入日期
     * @return 返回值
     */
    private String getFormatDate(Date date) {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (date != null) {
            return sdf.format(date);
        }
        return "";
    }


    private void getInboundParams(WmsInventoryManagerParamDTO dto, HashMap<String, Object> params) {
        params.put("orderBy", StringUtils.isBlank(dto.getOrder()) ? "t.od_id desc" : dto.getOrder());
        params.put("vehicleName", dto.getVehicleSpecName());
        params.put("vin", dto.getVin());
        params.put("materialName", dto.getMaterialName());
        //如果有明确要查询哪个客户的数据
        List<WmsCustomerWarehouseCodeEnum> enums = WmsCustomerWarehouseCodeEnum.getListForByCustomer(dto.getCustomerCode());
        //查询对应客户下所有的仓库数据
        getCustomerWarehouseCodeSQLSplit(params, enums);
        //客户有明确指定查询客户下的指定数据
        if (StringUtils.isNotBlank(dto.getWhCode())) {
            params.put("whCode", dto.getWhCode());
        }
        if (StringUtils.isNotBlank(dto.getStartTime())) {
            params.put("startTime", dto.getStartTime() + " 00:00:00");
        }
        if (StringUtils.isNotBlank(dto.getEndTime())) {
            params.put("endTime", dto.getEndTime() + " 23:59:59");
        }
    }

    private void getOutboundParams(WmsInventoryManagerParamDTO dto, HashMap<String, Object> params) {
        params.put("orderBy", StringUtils.isBlank(dto.getOrder()) ? "a.ot_id desc" : dto.getOrder());
        params.put("finishStatus", WmsOutboundTaskStatusEnum.WMS_OUTBOUND_TASK_STATUS_FINISHED.getValue());
        params.put("notDel", DeleteFlagEnum.NORMAL.getValue());
        params.put("notQuit", TaskQuitFlagEnum.NOT_QUIT.getValue());
        params.put("materialName", dto.getMaterialName());
        params.put("vehicleSpecName", dto.getVehicleSpecName());
        if (StringUtils.isNotBlank(dto.getVin())) {
            params.put("vin", dto.getVin());
        }
        if (StringUtils.isNotBlank(dto.getStartTime())) {
            params.put("startTime", dto.getStartTime() + " 00:00:00");
        }
        if (StringUtils.isNotBlank(dto.getEndTime())) {
            params.put("endTime", dto.getEndTime() + " 23:59:59");
        }
        List<WmsCustomerWarehouseCodeEnum> enums = WmsCustomerWarehouseCodeEnum.getListForByCustomer(dto.getCustomerCode());
        if (CollectionUtils.isNotEmpty(enums)) {
            StringBuilder sb = new StringBuilder();
            sb.append("(");
            enums.forEach((WmsCustomerWarehouseCodeEnum v) -> {
                sb.append(" a.ot_wh_code ='").append(v.getWarehouseCode()).append("' or ");
            });
            sb.append(")");
            String sqlSplit = StringUtils.substringBefore(String.valueOf(sb), "or )") + " )";
            params.put("customerCode", sqlSplit);
        }
        //如果有明确要查询哪个客户的数据
        if (StringUtils.isNotBlank(dto.getWhCode())) {
            params.put("whCode", dto.getWhCode());
        }
        params.put("notChange", TaskIsNeedChangeEnum.NO_CHANGE.getCode());
        params.put("cantChange", TaskIsNeedChangeEnum.NOT_CAR_TO_CHANGE.getCode());
    }

    private void getInventoryParams(WmsInventoryManagerParamDTO dto, HashMap<String, Object> params) {
        params.put("notDel", DeleteFlagEnum.NORMAL.getValue());
        params.put("orderBy", StringUtils.isBlank(dto.getOrder()) ? "a.inv_id desc" : dto.getOrder());
        params.put("materialName", dto.getMaterialName());
        params.put("vehicleSpecName", dto.getVehicleSpecName());
        params.put("status", dto.getStatus());
        if (StringUtils.isNotBlank(dto.getVin())) {
            params.put("vin", dto.getVin());
        }
        if (StringUtils.isNotBlank(dto.getStartTime())) {
            params.put("startTime", dto.getStartTime() + " 00:00:00");
        }
        if (StringUtils.isNotBlank(dto.getEndTime())) {
            params.put("endTime", dto.getEndTime() + " 23:59:59");
        }
        List<WmsCustomerWarehouseCodeEnum> enums = WmsCustomerWarehouseCodeEnum.getListForByCustomer(dto.getCustomerCode());
        if (CollectionUtils.isNotEmpty(enums)) {
            StringBuilder sb = new StringBuilder();
            sb.append("(");
            enums.forEach((WmsCustomerWarehouseCodeEnum v) -> {
                sb.append(" a.inv_wh_code ='").append(v.getWarehouseCode()).append("' or ");
            });
            sb.append(")");
            String sqlSplit = StringUtils.substringBefore(String.valueOf(sb), "or )") + " )";
            params.put("customerCode", sqlSplit);
        }
        //如果有明确要查询哪个客户的数据
        if (StringUtils.isNotBlank(dto.getWhCode())) {
            params.put("whCode", dto.getWhCode());
        }
    }


    private void updateSetWarehouseAge(List<WmsInventoryManagerResultDTO> data) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if (CollectionUtils.isNotEmpty(data)) {
            Date nowDate = new Date();
            if (CollectionUtils.isNotEmpty(data)) {
                data.stream()
                        .filter(eachData -> eachData != null && eachData.getGmtCreate() != null)
                        .forEach((WmsInventoryManagerResultDTO eachData) -> {
                            try {
                                int between = DateUtils.daysBetween(eachData.getGmtCreate(), nowDate);
                                if (between >= 0) {
                                    eachData.setWarehouseAge(between + 1);
                                }
                            } catch (ParseException e) {
                                logger.error("WmsInventoryVerificationServiceImpl.getInventoryManageRecordsByCustomer error:", e);
                                throw new BusinessException("库存时间格式转换异常!");
                            }
                        });
            }
        }
    }

    private void updateSetOutStream(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", BrowerEncodeingUtils.getContentDisposition("InboundRecordImportExcel.xls", request));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            logger.error("WmsInventoryVerificationServiceImpl.IOException:", e);
            throw new BusinessException("下载文件异常!");
        } finally {
            workbook.close();
        }
    }
}
